﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CLEX.Service.DataContracts;

namespace CLEX.Database.DAL
{
    public sealed class UserDAL : IUserDAL
    {
        private const string USER_ID_COL = "user_id";
        private const string USERNAME_COL = "username";
        private const string HASHED_PASSWORD_COL = "hashed_password";
        private const string PASSWORD_SALT_COL = "password_salt";
        private const string IS_ACTIVE_COL = "is_active";

        private const string DELETE_SP = "clexa.sp_user_delete";
        private const string INSERT_SP = "clexa.sp_user_insert";
        private const string SELECT_SP = "clexa.sp_user_select";
        private const string SELECT_BY_USER_ID_SP = "clexa.sp_user_select_by_user_id";
        private const string SELECT_BY_USERNAME_SP = "clexa.sp_user_select_by_username";
        private const string UPDATE_SP = "clexa.sp_user_update";

        private readonly string connectionString;

        public UserDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void Delete(int userId)
        {
            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_ID_COL, userId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public int Insert(string username, string hashedPassword, string passwordSalt, bool isActive)
        {
            int result;

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(INSERT_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter userIdParam = cmd.CreateParameter();
                    userIdParam.Direction = ParameterDirection.Output;
                    userIdParam.ParameterName = USER_ID_COL;
                    userIdParam.Size = 4;
                    userIdParam.DbType = DbType.Int32;
                    cmd.Parameters.Add(userIdParam);
                    cmd.Parameters.AddWithValue(USERNAME_COL, username);
                    cmd.Parameters.AddWithValue(HASHED_PASSWORD_COL, hashedPassword);
                    cmd.Parameters.AddWithValue(PASSWORD_SALT_COL, passwordSalt);
                    cmd.Parameters.AddWithValue(IS_ACTIVE_COL, isActive);

                    cmd.ExecuteNonQuery();

                    result = (int)userIdParam.Value;
                }
            }

            return result;
        }

        public List<UserFullData> Select()
        {
            List<UserFullData> result = new List<UserFullData>();

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(SELECT_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int userIdOrd = reader.GetOrdinal(USER_ID_COL);
                        int usernameOrd = reader.GetOrdinal(USERNAME_COL);
                        int hashedPasswordOrd = reader.GetOrdinal(HASHED_PASSWORD_COL);
                        int passwordSaltOrd = reader.GetOrdinal(PASSWORD_SALT_COL);
                        int isActiveOrd = reader.GetOrdinal(IS_ACTIVE_COL);

                        while (reader.Read())
                        {
                            result.Add(new UserFullData(
                                userId: reader.GetInt32(userIdOrd),
                                username: reader.GetString(usernameOrd),
                                hashedPassword: reader.GetString(hashedPasswordOrd),
                                passwordSalt: reader.GetString(passwordSaltOrd),
                                isActive: reader.GetBoolean(isActiveOrd)
                            ));
                        }
                    }
                }
            }

            return result;
        }

        public UserFullData SelectById(int userId)
        {
            UserFullData result;

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(SELECT_BY_USER_ID_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue(USER_ID_COL, userId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int userIdOrd = reader.GetOrdinal(USER_ID_COL);
                        int usernameOrd = reader.GetOrdinal(USERNAME_COL);
                        int hashedPasswordOrd = reader.GetOrdinal(HASHED_PASSWORD_COL);
                        int passwordSaltOrd = reader.GetOrdinal(PASSWORD_SALT_COL);
                        int isActiveOrd = reader.GetOrdinal(IS_ACTIVE_COL);

                        if (reader.Read())
                        {
                            result = new UserFullData(
                                    userId: reader.GetInt32(userIdOrd),
                                    username: reader.GetString(usernameOrd),
                                    hashedPassword: reader.GetString(hashedPasswordOrd),
                                    passwordSalt: reader.GetString(passwordSaltOrd),
                                    isActive: reader.GetBoolean(isActiveOrd)
                                );
                        }
                        else
                        {
                            result = null;
                        }
                    }
                }
            }

            return result;
        }

        public UserFullData SelectByUsername(string username)
        {
            UserFullData result;

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(SELECT_BY_USERNAME_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue(USERNAME_COL, username);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int userIdOrd = reader.GetOrdinal(USER_ID_COL);
                        int usernameOrd = reader.GetOrdinal(USERNAME_COL);
                        int hashedPasswordOrd = reader.GetOrdinal(HASHED_PASSWORD_COL);
                        int passwordSaltOrd = reader.GetOrdinal(PASSWORD_SALT_COL);
                        int isActiveOrd = reader.GetOrdinal(IS_ACTIVE_COL);

                        if (reader.Read())
                        {
                            result = new UserFullData(
                                    userId: reader.GetInt32(userIdOrd),
                                    username: reader.GetString(usernameOrd),
                                    hashedPassword: reader.GetString(hashedPasswordOrd),
                                    passwordSalt: reader.GetString(passwordSaltOrd),
                                    isActive: reader.GetBoolean(isActiveOrd)
                                );
                        }
                        else
                        {
                            result = null;
                        }
                    }
                }
            }

            return result;
        }

        public void Update(UserFullData user)
        {
            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_ID_COL, user.UserId);
                    cmd.Parameters.AddWithValue(USERNAME_COL, user.Username);
                    cmd.Parameters.AddWithValue(HASHED_PASSWORD_COL, user.HashedPassword);
                    cmd.Parameters.AddWithValue(PASSWORD_SALT_COL, user.PasswordSalt);
                    cmd.Parameters.AddWithValue(IS_ACTIVE_COL, user.IsActive);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
